﻿using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CommTest.T4
{
    internal class T4Code
    {
        private void WriteLine(string v, string name, string type = "")
        {
        }

        //static string _dbName = "Organization";
        static string _dbName = "iSchoolUser";
        static string _namespace = "Sxb.MsgSender.Common.Entity";
        static string _dirName = "T4";
        string GetAttr(MyFiled f)
        {
            //return $"\t\t[Column(\"{f.Name}\")] ";
            return $"\t\t[Display(\"{f.Name}\")] ";
        }
        List<string> FilterTable(List<string> tables)
        {
            return tables;//.Where(s => s.Contains("Template") || s.Contains("DeviceToken")).ToList();
        }

        public static void Test()
        {
            TestFormat();

            var t4 = new T4Code();
            t4.Create();

            var currProcess = Process.GetCurrentProcess();

            var baseDir = AppDomain.CurrentDomain.BaseDirectory;
            var t4Dir = Path.Combine(baseDir, _dirName);
            //Process.Start("explorer", t4Dir);
        }

        public static void TestFormat()
        {
            var t4 = new T4Code();
            var result = t4.FormatColumns("userId");
            Debug.Assert(result == "UserId");

            result = t4.FormatColumns("user_id");
            Debug.Assert(result == "UserId");

            result = t4.FormatColumns("_user_id");
            Debug.Assert(result == "UserId");

            result = t4.FormatColumns("User_id");
            Debug.Assert(result == "UserId");

            result = t4.FormatColumns("User_Id");
            Debug.Assert(result == "UserId");

            result = t4.FormatColumns("USER_ID");
            Debug.Assert(result == "USERID");
        }

        public void Create()
        {
            string dbconnectionStr = $"data source=192.168.31.13;initial catalog={_dbName};user id=ischool;password=SxbLucas$0769;MultipleActiveResultSets=True";
            string rootPath = AppDomain.CurrentDomain.BaseDirectory;
            if (!Directory.Exists(rootPath + @"\" + _dirName))
            {
                Directory.CreateDirectory(rootPath + @"\" + _dirName);
            }


            string filePath = rootPath + @"\" + _dirName + @"\{name}.cs";

            List<string> tables = GetTables(dbconnectionStr);
            tables = FilterTable(tables);

            foreach (var t in tables)
            {
                WriteLine("-生成{0}表", t);
                var fileName = NeedFormat(t) ? FormatColumns(t) : t;
                string file = filePath.Replace("{name}", fileName);
                var odic = GetFileds(t, dbconnectionStr);
                StartNewFile(file, _namespace, t, odic);
            }
        }



        public bool IsLower(char c)
        {
            //a-z(asc >= 97 && asc <= 122)
            //A-Z(asc >= 65 && asc <= 90)
            return c >= 97 && c <= 122;
        }

        public bool NeedFormat(string str)
        {
            return str != null && str.Length > 0
                && (str.Contains("_") || IsLower(str[0]));
        }

        public string FormatColumns(string str)
        {
            if (str == null)
            {
                return null;
            }

            var firstChar = true;
            List<char> chars = new List<char>();
            foreach (var c in str)
            {
                if (c == '_')
                {
                    firstChar = true;
                    continue;
                }
                if (firstChar && IsLower(c))
                    chars.Add((char)(c - 32));
                else
                    chars.Add(c);
                firstChar = false;

            }
            return new string(chars.ToArray());
        }


        public class MyFiled
        {
            public string Name { get; set; }
            public string Type { get; set; }
            public string Description { get; set; }
            public int IsPrimary { get; set; }
            public int IsIdentity { get; set; }
        }
        public List<string> GetTables(string connstr)
        {
            List<string> tables = new List<string>();
            string sql = "	Select Name FROM SysObjects Where XType='U' or XType='V'  Order BY Name ";
            using (SqlConnection con = new SqlConnection(connstr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                var reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        string tableName = reader.GetString(0);
                        tables.Add(tableName);

                    }
                    reader.Close();
                }
            }
            return tables;
        }

        public List<MyFiled> GetFileds(string tableName, string connstr)
        {
            List<MyFiled> result = new List<MyFiled>();
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT  sc.name,st.name,sc.length,sc.isnullable,");
            sql.AppendLine(" isIdentity = COLUMNPROPERTY( sc.id,sc.name,'IsIdentity'),");
            sql.AppendLine(" isPrimary =case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (     SELECT   name   FROM   sysindexes   WHERE   indid   in(     SELECT   indid   FROM   sysindexkeys   WHERE   id   =   sc.id   AND   colid=sc.colid     )))   then   1 else   0 end ");
            sql.AppendLine(" ,isnull(ep.value, '') as description ");
            sql.AppendLine("  FROM SysColumns sc");
            sql.AppendLine(" join systypes st on sc.xusertype = st.xusertype ");
            sql.AppendLine(" left join sys.extended_properties ep on ep.major_id = sc.id AND ep.minor_id = sc.colid ");
            sql.AppendFormat("  where sc.id = object_id('{0}') ", tableName);

            using (SqlConnection con = new SqlConnection(connstr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql.ToString(), con);
                var reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        string filedName = reader.GetString(0);
                        string typeName = reader.GetString(1);
                        int isnullable = (int)reader[3];
                        int isIdentity = (int)reader[4];
                        int isPrimary = (int)reader[5];
                        string description = reader.GetString(6);

                        var filed = new MyFiled();
                        filed.Name = filedName;
                        filed.Type = SqlServerDbTypeMapCSharp(typeName, isnullable);
                        filed.IsIdentity = isIdentity;
                        filed.IsPrimary = isPrimary;
                        filed.Description = description;
                        result.Add(filed);

                    }
                    reader.Close();
                }
            }

            return result;
        }

        public void StartNewFile(string filePath, string _namespace, string _tableName, List<MyFiled> fields)
        {
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            var block = Encoding.UTF8.GetBytes(EndBlock(_namespace, _tableName, fields));
            fs.Write(block, 0, block.Length);
            fs.Close();
        }

        public string EndBlock(string _namespace, string _tableName, List<MyFiled> fields)
        {
            var _class = NeedFormat(_tableName) ? FormatColumns(_tableName) : _tableName;

            StringBuilder currentBlock = new StringBuilder();
            currentBlock.AppendLine("using System;");
            currentBlock.AppendLine("using System.Collections.Generic;");
            //currentBlock.AppendLine("using Dapper.Contrib.Extensions;");
            currentBlock.AppendLine("using Kogel.Dapper.Extension.Attributes;");
            currentBlock.AppendLine();
            currentBlock.AppendFormat("namespace {0}", _namespace);
            currentBlock.AppendLine();
            currentBlock.Append("{");
            currentBlock.AppendLine();

            currentBlock.AppendLine($"\t/// <summary> ");
            currentBlock.AppendLine($"\t///  ");
            currentBlock.AppendLine($"\t/// </summary> ");

            currentBlock.Append("\t[Serializable]");
            currentBlock.AppendLine();
            //currentBlock.AppendFormat("\t[Table(\"{0}\")]", _class);
            //currentBlock.AppendLine();
            currentBlock.AppendFormat("\t[Display(Rename = \"[{0}]\")]", _tableName);
            currentBlock.AppendLine();
            currentBlock.AppendFormat("\tpublic partial class {0}", _class);
            currentBlock.AppendLine();
            currentBlock.Append("\t{");
            currentBlock.AppendLine();

            foreach (var f in fields)
            {
                WriteLine("\t|_生成{0}字段 类型{1}", f.Name, f.Type);
                string[] descriptLins = f.Description.Split(new[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
                currentBlock.AppendLine($"\t\t/// <summary> ");
                foreach (var dl in descriptLins)
                {
                    currentBlock.AppendLine($"\t\t/// {dl} ");
                }

                currentBlock.AppendLine($"\t\t/// </summary> ");
                if (f.IsPrimary == 1)
                {
                }
                //if (f.IsIdentity == 1 && f.IsPrimary == 1)
                //{
                //    currentBlock.AppendLine("\t\t[Identity] ");
                //    currentBlock.AppendLine("\t\t[Key]  ");
                //}
                //if (f.IsPrimary == 1 && f.IsIdentity == 0)
                //{
                //    currentBlock.AppendLine("\t\t[ExplicitKey]");
                //}
                //if (f.IsPrimary == 0 && f.IsIdentity == 1)
                //{
                //    currentBlock.AppendLine("\t\t[Identity] ");
                //    currentBlock.AppendLine("\t\t[Write(false)] ");
                //}

                if (NeedFormat(f.Name))
                {
                    currentBlock.AppendLine(GetAttr(f));
                    currentBlock.AppendFormat("\t\tpublic {1} {0} ", KeyWordTran(FormatColumns(f.Name)), f.Type);
                }
                else
                {
                    currentBlock.AppendFormat("\t\tpublic {1} {0} ", KeyWordTran(f.Name), f.Type);
                }

                currentBlock.AppendLine("{ get; set; }");
                currentBlock.AppendLine();
            }
            currentBlock.AppendLine();
            currentBlock.Append("\t}");
            currentBlock.AppendLine();
            currentBlock.Append("}");
            return currentBlock.ToString();
        }

        public string KeyWordTran(string v)
        {
            string tv = v.Trim();
            switch (tv)
            {
                case "checked": return "@checked";
                default: return tv;
            }
        }


        public string SqlServerDbTypeMapCSharp(string dbtype, int isnullable)
        {
            string csharpType = string.Empty;


            switch (dbtype.ToLower())
            {
                case "bigint": csharpType = isnullable == 1 ? "long?" : "long"; break;
                case "binary": csharpType = "byte[]"; break;
                case "bit": csharpType = isnullable == 1 ? "bool?" : "bool"; break;
                case "char": csharpType = "string"; break;
                case "date":
                case "datetime":
                case "datetime2": csharpType = isnullable == 1 ? "DateTime?" : "DateTime"; break;
                case "datetimeoffset": csharpType = "DateTimeOffset"; break;
                case "decimal": csharpType = "decimal"; break;
                case "float": csharpType = "double"; break;
                case "image": csharpType = "byte[]"; break;
                case "int": csharpType = isnullable == 1 ? "int?" : "int"; break;
                case "money": csharpType = "decimal"; break;
                case "nchar": csharpType = "string"; break;
                case "ntext": csharpType = "string"; break;
                case "numeric": csharpType = "decimal"; break;
                case "nvarchar": csharpType = "string"; break;
                case "real": csharpType = "Single"; break;
                case "smalldatetime": csharpType = "DateTime"; break;
                case "smallint": csharpType = "short"; break;
                case "smallmoney": csharpType = "decimal"; break;
                case "sql_variant": csharpType = "object"; break;
                case "sysname": csharpType = "object"; break;
                case "text": csharpType = "string"; break;
                case "time": csharpType = "TimeSpan"; break;
                case "timestamp": csharpType = "byte[]"; break;
                case "tinyint": csharpType = "byte"; break;
                case "uniqueidentifier": csharpType = isnullable == 1 ? "Guid?" : "Guid"; break;
                case "varbinary": csharpType = "byte[]"; break;
                case "varchar": csharpType = "string"; break;
                case "xml": csharpType = "string"; break;
                default: csharpType = "object"; break;
            }
            return csharpType;
        }

    }
}
